const pool = require('../config/database');

class UserModel {
  /**
   * 创建新用户
   * @param {Object} userData - 用户数据
   * @returns {Promise<Object>} 创建的用户信息
   */
  static async create(userData) {
    const { username, password, email, fullName, role = 'user', phone, department } = userData;
    
    const sql = `
      INSERT INTO users (username, password, email, full_name, role, phone, department, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
    `;
    
    const [result] = await pool.execute(sql, [
      username, password, email, fullName, role, phone, department
    ]);
    
    return {
      id: result.insertId,
      username,
      email,
      fullName,
      role,
      phone,
      department
    };
  }

  /**
   * 根据ID查找用户
   * @param {number} id - 用户ID
   * @returns {Promise<Object|null>} 用户信息
   */
  static async findById(id) {
    const sql = `
      SELECT id, username, email, full_name, role, phone, department, 
             created_at, updated_at, last_login
      FROM users 
      WHERE id = ? AND deleted_at IS NULL
    `;
    
    const [rows] = await pool.execute(sql, [id]);
    return rows.length > 0 ? rows[0] : null;
  }

  /**
   * 根据用户名查找用户（包含密码，用于登录验证）
   * @param {string} username - 用户名
   * @returns {Promise<Object|null>} 用户信息
   */
  static async findByUsername(username) {
    const sql = `
      SELECT id, username, password, email, full_name, role, phone, department,
             created_at, updated_at, last_login
      FROM users 
      WHERE username = ? AND deleted_at IS NULL
    `;
    
    const [rows] = await pool.execute(sql, [username]);
    return rows.length > 0 ? rows[0] : null;
  }

  /**
   * 查找所有用户（支持分页和筛选）
   * @param {Object} filters - 筛选条件
   * @returns {Promise<Object>} 用户列表和总数
   */
  static async findAll(filters = {}) {
    const { page = 1, limit = 10, role, department, search } = filters;
    const offset = (page - 1) * limit;
    
    let whereConditions = ['deleted_at IS NULL'];
    let params = [];
    
    if (role) {
      whereConditions.push('role = ?');
      params.push(role);
    }
    
    if (department) {
      whereConditions.push('department = ?');
      params.push(department);
    }
    
    if (search) {
      whereConditions.push('(username LIKE ? OR full_name LIKE ? OR email LIKE ?)');
      const searchPattern = `%${search}%`;
      params.push(searchPattern, searchPattern, searchPattern);
    }
    
    const whereClause = whereConditions.join(' AND ');
    
    // 查询总数
    const countSql = `SELECT COUNT(*) as total FROM users WHERE ${whereClause}`;
    const [countResult] = await pool.execute(countSql, params);
    const total = countResult[0].total;
    
    // 查询用户列表
    const sql = `
      SELECT id, username, email, full_name, role, phone, department,
             created_at, updated_at, last_login
      FROM users 
      WHERE ${whereClause}
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const [rows] = await pool.execute(sql, [...params, limit, offset]);
    
    return {
      users: rows,
      pagination: {
        total,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(total / limit)
      }
    };
  }

  /**
   * 更新用户信息
   * @param {number} id - 用户ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<boolean>} 更新是否成功
   */
  static async update(id, updateData) {
    const allowedFields = ['username', 'email', 'full_name', 'role', 'phone', 'department', 'password'];
    const updateFields = [];
    const params = [];
    
    Object.keys(updateData).forEach(key => {
      if (allowedFields.includes(key) && updateData[key] !== undefined) {
        updateFields.push(`${key} = ?`);
        params.push(updateData[key]);
      }
    });
    
    if (updateFields.length === 0) {
      throw new Error('没有有效的更新字段');
    }
    
    updateFields.push('updated_at = NOW()');
    params.push(id);
    
    const sql = `UPDATE users SET ${updateFields.join(', ')} WHERE id = ? AND deleted_at IS NULL`;
    const [result] = await pool.execute(sql, params);
    
    return result.affectedRows > 0;
  }

  /**
   * 软删除用户
   * @param {number} id - 用户ID
   * @returns {Promise<boolean>} 删除是否成功
   */
  static async remove(id) {
    const sql = `UPDATE users SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL`;
    const [result] = await pool.execute(sql, [id]);
    
    return result.affectedRows > 0;
  }

  /**
   * 更新最后登录时间
   * @param {number} id - 用户ID
   * @returns {Promise<boolean>} 更新是否成功
   */
  static async updateLastLogin(id) {
    const sql = `UPDATE users SET last_login = NOW() WHERE id = ?`;
    const [result] = await pool.execute(sql, [id]);
    
    return result.affectedRows > 0;
  }
}

module.exports = UserModel;